﻿DELIMITER //

--
--
DROP PROCEDURE IF EXISTS spZeusPackageAdd
//

-- HU - Rakomány felvétel
-- EN - Package insert
--
--
CREATE PROCEDURE spZeusPackageAdd
  (_Lang VARCHAR(10), _SupplyID INTEGER, _Code VARCHAR(20), _Ean VARCHAR(128), _Quantity DECIMAL(18,6))
BEGIN
  DECLARE _Rows INTEGER;

  DECLARE _ID INTEGER;
  DECLARE _UserID INTEGER;

  DECLARE _ArticleCode VARCHAR(20);
  DECLARE _ArticleName VARCHAR(256);
  DECLARE _UnitCode VARCHAR(10);
  DECLARE _Expired VARCHAR(10);
  DECLARE _AI01 VARCHAR(16);

  DECLARE _EanXML VARCHAR(256);

  -- 
  IF (_SupplyID IS NULL) OR (_SupplyID = 0) THEN

    SET _SupplyID = NULL; 
  END IF;

    
  -- EAN8, EAN13, GTIN
  --
  IF (LENGTH(_EAN) <= 13) THEN

    BEGIN
      DECLARE EXIT HANDLER FOR NOT FOUND SET _Rows = 0;
  
      SELECT Code, Name, Unit, ''
        INTO _ArticleCode, _ArticleName, _UnitCode, _Expired
        FROM ZeusArticle
       WHERE EAN = _EAN;
  
      SET _Rows = 1;
    END;
  
  ELSE

    -- Parse UCC/EAN-128
    --

    SET _EanXML = fnEAN128(_Ean);

    SELECT 
          CASE 
            WHEN AI01 != '' THEN AI01
            WHEN AI02 != '' THEN AI02
          ELSE '' END,
          CASE 
            WHEN AI15 != '' THEN CONCAT('20', AI15)
            WHEN AI17 != '' THEN CONCAT('20', AI17)
          ELSE '' END
      FROM
      (
       SELECT 
            ExtractValue(_EanXML, '/Ean128/AI01[1]') AI01,
            ExtractValue(_EanXML, '/Ean128/AI02[1]') AI02,
            ExtractValue(_EanXML, '/Ean128/AI15[1]') AI15,
            ExtractValue(_EanXML, '/Ean128/AI17[1]') AI17
      ) AS t

      INTO _AI01, _Expired;

      BEGIN
        DECLARE EXIT HANDLER FOR NOT FOUND SET _Rows = 0;
    
        SELECT Code, Name, Unit
          INTO _ArticleCode, _ArticleName, _UnitCode
          FROM ZeusArticle
         WHERE EAN = _AI01;
    
        SET _Rows = 1;
      END;

  END IF;

  IF (_Rows != 1) THEN

    IF (_Lang = 'HU') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = N'Nincs ilyen cikk ezzel az EAN kóddal !';
    END IF;

    IF (_Lang = 'EN') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'There is no such article with this EAN ! ';
    END IF;
  END IF;

  IF (IFNULL(_Quantity, 0) < 0) THEN

    IF (_Lang = 'HU') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = N'Mennyiség nem lehet negatív !';
    END IF;

    IF (_Lang = 'EN') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = N'Quantity can not be negative !';
    END IF;
  END IF;

  BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET _Rows = 0;
        
    INSERT INTO ZeusPackage
      (SupplyID,  Code, ArticleCode, ArticleName, Expired, Quantity, UnitCode, Refill)
    VALUES
      (_SupplyID, _Code, _ArticleCode, _ArticleName, _Expired, _Quantity, _UnitCode, 'N');
     
    SET _Rows = 1;
  END;

  IF (_Rows != 1) THEN
    IF (_Lang = 'HU') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Adatok mentése nem sikerült ! ';
    END IF;

    IF (_Lang = 'EN') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Package save failed ! ';
    END IF;

  END IF;

END
//
